package com.edu.hre.db.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.apache.log4j.Logger;

import com.edu.hre.db.config.BaseDAO;
import com.edu.hre.db.config.DBPool;
import com.edu.hre.db.dto.HreEmployee;

public class EmployeeDAO extends BaseDAO {

	private static final Logger logger = Logger.getLogger(EmployeeDAO.class);

	@SuppressWarnings("deprecation")
	public List<HreEmployee> getAllEmployees(int offset, int ps) {

		List<HreEmployee> listEmps = new ArrayList<HreEmployee>();
		EmployeesListInfo eli = new EmployeesListInfo();
		Connection conn = null;
		PreparedStatement preStmt = null;
		ResultSet rs = null;
		try {
			conn = DBPool.getConnection();
			String strSQLQuery = "select e.*"
					+ ",n.name as emp_nationality"
					+ ",p.province_vn_name as emp_province"
					+ ",d.district_name as emp_district "
					+ ",s.name as emp_subunit "
					+ "from hre_employee e "
					+ "LEFT JOIN hre_nationality n ON e.nation_code = n.id "
					+ "LEFT JOIN hre_province p ON e.provin_code = p.province_code "
					+ "LEFT JOIN hre_district d ON e.district_code = d.district_code "
					+ "LEFT JOIN hre_subunit s ON e.work_station = s.id "
					+ "WHERE e.is_deleted = 0 " 
					+ "ORDER BY e.emp_firstname "
					+ "LIMIT " + offset + "," + ps;
			preStmt = conn.prepareStatement(strSQLQuery);
			
			rs = preStmt.executeQuery();
			while (rs.next()) {
				HreEmployee employee = new HreEmployee();
				employee.setEmpFirstname(rs.getString("emp_firstname"));
				employee.setEmpLastname(rs.getString("emp_lastname"));
				employee.setEmpMiddleName(rs.getString("emp_middle_name"));
				employee.setEmpNickName(rs.getString("emp_nick_name"));
				employee.setEmpIdCardNum(rs.getString("emp_id_card_num"));
				employee.setEmpIdCardPlace(rs.getString("emp_id_card_place"));
				Date d = null;
				if(rs.getDate("emp_id_card_date") != null){
					d= (rs.getDate("emp_id_card_date"));
					d.setYear(d.getYear()-1900);
				}
				employee.setEmpIdCardDate(d);
				employee.setEmpGender(rs.getShort("emp_gender"));
				employee.setEmpNationality(rs.getString("emp_nationality"));
				if(rs.getDate("emp_birthday") != null){
					d= (rs.getDate("emp_birthday"));
					d.setYear(d.getYear()-1900);
				}
				employee.setEmpBirthday(d);
				employee.setEmpMaritalStatus(rs.getString("emp_marital_status"));
				employee.setEmpMilitaryService(rs.getString("emp_military_service"));
				employee.setEmpStreet1(rs.getString("emp_street1"));
				employee.setEmpStreet2(rs.getString("emp_street2"));
				employee.setEmpZipcode(rs.getString("emp_zipcode"));
				employee.setEmpProvince(rs.getString("emp_province"));
				employee.setEmpDistrict(rs.getString("emp_district"));
				employee.setEmpHmTelephone(rs.getString("emp_hm_telephone"));
				employee.setEmpMobile(rs.getString("emp_mobile"));
				employee.setEmpWorkTelephone(rs.getString("emp_work_telephone"));
				employee.setEmpWorkEmail(rs.getString("emp_work_email"));
				employee.setEmpOthEmail(rs.getString("emp_oth_email"));
				employee.setEmpNumber(rs.getInt("emp_number"));
				employee.setEmpSubUnit(rs.getString("emp_subunit"));

				// .....
				// .....
				listEmps.add(employee);
			}
			
		} catch (Exception e) {
			e.printStackTrace();
			logger.error(e.getMessage());
		} finally {
			// đóng PreparedStatement, ResultSet và đẩy connection vào
			// pool
			releaseConnection(conn, preStmt, rs);
		}
		
		return listEmps;
	}

	public List<HreEmployee> getSupevisors(int employeeNumber) {
		List<HreEmployee> listEmps = new ArrayList<HreEmployee>();
		Connection conn = null;
		PreparedStatement preStmt = null;
		ResultSet rs = null;
		try {
			conn = DBPool.getConnection();
			String strSQLQuery = "SELECT e.*, "
					+ "rm.reporting_method_name "
					+ "FROM hre_emp_reportto r "
					+ "JOIN hre_emp_reporting_method rm ON r.erep_reporting_mode = rm.reporting_method_id "
					+ "JOIN hre_employee e ON e.emp_number = r.erep_sup_emp_number "
					+ "WHERE e.is_deleted = 0 AND r.erep_sub_emp_number ="
					+ employeeNumber;
			preStmt = conn.prepareStatement(strSQLQuery);
			rs = preStmt.executeQuery();
			while (rs.next()) {
				HreEmployee employee = new HreEmployee();
				employee.setEmpFirstname(rs.getString("emp_firstname"));
				employee.setEmpLastname(rs.getString("emp_lastname"));
				employee.setEmpMiddleName(rs.getString("emp_middle_name"));
				employee.setEmpNumber(rs.getInt("emp_number"));
				employee.setReportingMethod(rs
						.getString("reporting_method_name"));
				listEmps.add(employee);
			}
		} catch (Exception e) {
			e.printStackTrace();
			logger.error(e.getMessage());
		} finally {
			// đóng PreparedStatement, ResultSet và đẩy connection vào
			// pool
			releaseConnection(conn, preStmt, rs);
		}
		return listEmps;
	}
	public List<HreEmployee> getSubordinates(int employeeNumber) {
		List<HreEmployee> listEmps = new ArrayList<HreEmployee>();
		Connection conn = null;
		PreparedStatement preStmt = null;
		ResultSet rs = null;
		try {
			conn = DBPool.getConnection();
			String strSQLQuery = "SELECT e.*,"
					+ "rm.reporting_method_name "
					+ "FROM hre_emp_reportto r "
					+ "JOIN hre_emp_reporting_method rm ON r.erep_reporting_mode = rm.reporting_method_id "
					+ "JOIN hre_employee e ON e.emp_number = r.erep_sub_emp_number "
					+ "WHERE e.is_deleted = 0 AND r.erep_sup_emp_number ="
					+ employeeNumber;
			preStmt = conn.prepareStatement(strSQLQuery);
			rs = preStmt.executeQuery();
			while (rs.next()) {
				HreEmployee employee = new HreEmployee();
				employee.setEmpFirstname(rs.getString("emp_firstname"));
				employee.setEmpLastname(rs.getString("emp_lastname"));
				employee.setEmpMiddleName(rs.getString("emp_middle_name"));
				employee.setReportingMethod(rs
						.getString("reporting_method_name"));
				listEmps.add(employee);
			}
		} catch (Exception e) {
			e.printStackTrace();
			logger.error(e.getMessage());
		} finally {
			// đóng PreparedStatement, ResultSet và đẩy connection vào
			// pool
			releaseConnection(conn, preStmt, rs);
		}
		return listEmps;
	}

}
